package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import com.hualude.roadsign.model.GuardrailEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by pjn on 2017/8/14.
 */
public interface GuardrailRepository extends CrudRepository<GuardrailEntity, Long> {

    /**
     * @Description: 护栏详情2Fault
     * @Author: pan-jianan
     * @Date: 2017/8/14 19:58
     */
    @Query(value = "select new com.hualude.roadsign.dto.GuardrailFaultDto(g.guardrailId,g.guardrailOrgId,g.guardrailMainOrgId," +
            "g.guardrailProvince,g.guardrailCity,g.guardrailArea,g.guardrailRoadName,g.guardrailStartRoad,g.guardrailEndRoad," +
            "g.guardrailType,g.guardrailMaterial,g.guardrailColor,g.guardrailPhoto,g.guardrailInstallTime," +
            "g.guardrailInstallOrg,g.guardrailPeriod, g.guardrailServiceLife, g.guardrailCreatorId, g.coordinateId,g.guardrailMileage) " +
            "from GuardrailEntity g where g.guardrailId=?1")
    public GuardrailFaultDto getGuardrailInfo2Fault(Integer guardrailId) throws Exception;

    /**
     * @Description: 护栏故障modal datatable
     * @Author: pan-jianan
     * @Date: 2017/8/14 20:22
     */
    public List<DTGuardrailFaultDto> getGuardrailFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String roadName, String startRoad, String endRoad) throws Exception;

    /**
     * @Description: 护栏获取第一个路名
     * @Author: pan-jianan
     * @Date: 2017/8/14 20:59
     */
    public List<String> getRoadURoadName(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 护栏根据第一个获取第二个路名
     * @Author: pan-jianan
     * @Date: 2017/8/14 20:59
     */
    public List<String> getSectionUStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 获取护栏信息4Dt
     * @author xu_jiaHui
     * @date 2017/8/18 21:00
     */
    public List<GuardrailDto> getGuardrail4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Boolean audit, String startRoad, String endRoad, String roadName) throws Exception;

    /**
     * @Description: 获取护栏数量4Dt
     * @author xu_jiaHui
     * @date 2017/8/18 21:01
     */
    public Integer getGuardrail4DTCount(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Boolean audit, String startRoad, String endRoad, String roadName) throws Exception;

    /**
     * @Description: 删除护栏
     * @author xu_jiaHui
     * @date 2017/8/18 21:02
     */
    @Modifying
    @Query(value = "delete from GuardrailEntity where guardrailId = ?1")
    public Integer deleteGuardrail(Integer guardrailId) throws Exception;

    /**
     * @Description: 审核护栏
     * @author xu_jiaHui
     * @date 2017/8/18 21:02
     */
    @Modifying
    @Query(value = "update GuardrailEntity set guardrailIsComplete = 1 where guardrailId = ?1")
    public Integer auditGuardrail(Integer guardrailId) throws Exception;

    @Modifying
    @Query(value = "update GuardrailEntity set projectNumber=?2,guardrailProvince=?3,guardrailCity=?4,guardrailArea=?5," +
            "guardrailRoadName=?6,guardrailStartRoad=?7,guardrailEndRoad=?8,guardrailType=?9,guardrailMaterial=?10," +
            "guardrailPhoto=?11,guardrailInstallTime=?12,guardrailInstallOrg=?13,guardrailPeriod=?14,guardrailServiceLife=?15," +
            "guardrailRemark=?16,guardrailCreatorId=?17,guardrailModifierId=?18,guardrailOrgId=?19,guardrailMainOrgId=?20," +
            "guardrailColor=?21,guardrailMileage=?22,guardrailIsComplete=0 where guardrailId=?1")
    public Integer updateGuardrail(Integer guardrailId,
                                   String projectNumber,
                                   String guardrailProvince,
                                   String guardrailCity,
                                   String guardrailArea,
                                   String guardrailRoadName,
                                   String guardrailStartRoad,
                                   String guardrailEndRoad,
                                   Integer guardrailType,
                                   Integer guardrailMaterial,
                                   String guardrailPhoto,
                                   String guardrailInstallTime,
                                   String guardrailInstallOrg,
                                   Integer guardrailPeriod,
                                   Integer guardrailServiceLife,
                                   String guardrailRemark,
                                   Integer guardrailCreatorId,
                                   Integer guardrailModifierId,
                                   Integer guardrailOrgId,
                                   Integer guardrailMainOrgId,
                                   Integer guardrailColor,
                                   String guardrailMileage) throws Exception;

    /**
     * @Description: 获取起始路名
     * @author xu_jiaHui
     * @date 2017/8/17 13:01
     */
    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception;

    /**
     * @Description: 获取结束路名
     * @author xu_jiaHui
     * @date 2017/8/17 13:05
     */
    public List<String> getEndRoadByStartRoad(String coverRoadName, String startRoad, String actorIds, Integer orgId) throws Exception;

    /**
     * @Description: 通过Id获取护栏信息
     * @author Jiang_Zhengyu
     * @date 2017/10/13 19:48
     */
    public GuardrailDto getGuardrailById(Integer guardrailId) throws Exception;


    @Query(value = "select new com.hualude.roadsign.dto.MapInfoDto(g.guardrailId,g.guardrailPhoto,g.projectNumber," +
            "g.guardrailProvince,g.guardrailCity,g.guardrailArea,g.guardrailRoadName,g.guardrailStartRoad," +
            "g.guardrailEndRoad,g.guardrailType,g.guardrailMaterial,g.guardrailColor,g.guardrailInstallTime,g.guardrailInstallOrg,g.guardrailPeriod," +
            "g.guardrailServiceLife,g.guardrailRemark,u.userName,o1.orgName,o2.orgName) from " +
            "GuardrailEntity g,OrgEntity o1,OrgEntity o2,UserEntity u where o1.orgId=g.guardrailOrgId and " +
            "o2.orgId=g.guardrailMainOrgId and g.guardrailCreatorId=u.userId and g.guardrailId=?1 ")
    public MapInfoDto getGuardrailInfoById(Integer elementId) throws Exception;

    /**
     * @Description: 获取护栏坐标信息用于地图显示
     * @author xu_jiaHui
     * @date 2017/8/19 17:07
     */
    public List<GuardrailDto> getGuardrailCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception;

    /**
     * @Description: 根据OrgId, actorId获取城市4App
     * @author xu_jiaHui
     * @date 2017/10/9 15:42
     */
    public List<String> getCity4Guardrail(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 根据city获取路口4App
     * @author xu_jiaHui
     * @date 2017/10/9 16:17
     */
    public List<String> getStartRoadByCity(String city, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 通过城市 起始路名 获取结束路名4App
     * @author xu_jiaHui
     * @date 2017/10/9 16:21
     */
    public List<String> getSectionByStartRoad4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 获取护栏信息用于列表展示4App
     * @author Jiang_Zhengyu
     * @date 2017/10/13 17:27
     */
    public List<GuardrailDto> getGuardrail4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName) throws Exception;

    /**
     * @Description: 通过城市获取路名
     * @author Jiang_Zhengyu
     * @date 2017/10/14 23:02
     */
    public List<String> getRoadName4GuardrailByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 根据城市路名起始路名 获取结束路名
     * @author Jiang_Zhengyu
     * @date 2017/10/14 23:03
     */
    public List<String> getEndRoadByStartRoad4App(String city, String RoadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize);

    /**
     * @Description: 根据城市 路名 获取起始路名
     * @author Jiang_Zhengyu
     * @date 2017/10/14 23:03
     */
    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: app地图
     * @Author: shao-yikun
     * @Date: 2017/11/23 上午9:27
     */
    public List<GuardrailDto> getGuardrail4Map4App(Integer orgId,
                                                   String actorIds,
                                                   double neLongitude,
                                                   double neLatitude,
                                                   double swLongitude,
                                                   double swLatitude) throws Exception;

}
